*library aliases;
libname tax69 'path1';
libname tax74 'path2';
libname tax79 'path3';
libname tax84 'path4';
libname tax89 'path5';
libname tax94 'path6';
libname out 'YOUR_FILEPATH';

/*call in data*/

PROC SQL;
CREATE TABLE WORK.AGI AS
SELECT 
'1969' AS Tax_Year,
INPUT(var48, 3.)*10**INPUT(var49, 1.) AS var50
FROM tax69.file1

UNION ALL

SELECT 
'1974' AS Tax_Year,
CASE WHEN var51 = '1' THEN -1*INPUT(var49, 3.)*10**INPUT(var48, 1.)
    ELSE INPUT(var49, 3.)*10**INPUT(var48, 1.)
    END AS var50
FROM tax74.file2

UNION ALL

SELECT
'1979' AS Tax_Year,
INPUT(var50,20.) AS var50
FROM tax79.file3

UNION ALL

SELECT
'1984' AS Tax_Year,
INPUT(var50,20.) AS var50
FROM tax84.file4

UNION ALL

SELECT
'1989' AS Tax_Year,
INPUT(var50,20.) AS var50
FROM tax89.file5

UNION ALL

SELECT
'1994' AS Tax_Year,
INPUT(var50,20.) AS var50
FROM tax94.file6
;
QUIT;

PROC SQL;
CREATE TABLE OUT.AGI_CATEGORIES AS
SELECT
Tax_Year,
COUNT(*) AS Total_Records FORMAT COMMA20.,
SUM(CASE WHEN var50 < 0 THEN 1 ELSE 0 END) AS AGI_Negative FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 0 AND var50 < 5000) THEN 1 ELSE 0 END) AS AGI_0to5000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 5000 AND var50 < 10000) THEN 1 ELSE 0 END) AS AGI_5000to10000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 10000 AND var50 < 15000) THEN 1 ELSE 0 END) AS AGI_10000to15000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 15000 AND var50 < 20000) THEN 1 ELSE 0 END) AS AGI_15000to20000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 20000 AND var50 < 30000) THEN 1 ELSE 0 END) AS AGI_20000to30000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 30000 AND var50 < 50000) THEN 1 ELSE 0 END) AS AGI_30000to50000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 50000 AND var50 < 100000) THEN 1 ELSE 0 END) AS AGI_50000to100000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 100000 AND var50 < 200000) THEN 1 ELSE 0 END) AS AGI_100000to200000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 200000 AND var50 < 500000) THEN 1 ELSE 0 END) AS AGI_200000to500000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 500000 AND var50 < 1000000) THEN 1 ELSE 0 END) AS AGI_500000to1M FORMAT COMMA20.,
SUM(CASE WHEN var50 >= 1000000 THEN 1 ELSE 0 END) AS AGI_1MorMore FORMAT COMMA20.
FROM WORK.AGI
GROUP BY Tax_Year;
QUIT;

PROC SQL;
CREATE TABLE OUT.AGI_SUMS AS
SELECT
Tax_Year,
COUNT(*) AS Total_Records FORMAT COMMA20.,
SUM(var50) AS Total_AGI FORMAT COMMA20.,
SUM(CASE WHEN var50 < 0 THEN var50 ELSE 0 END) AS AGI_Negative FORMAT COMMA20.,
SUM(CASE WHEN var50 >= 0 AND var50 < 5000 THEN var50 ELSE 0 END) AS AGI_0to5000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 5000 AND var50 < 10000) THEN var50 ELSE 0 END) AS AGI_5000to10000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 10000 AND var50 < 15000) THEN var50 ELSE 0 END) AS AGI_10000to15000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 15000 AND var50 < 20000) THEN var50 ELSE 0 END) AS AGI_15000to20000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 20000 AND var50 < 30000) THEN var50 ELSE 0 END) AS AGI_20000to30000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 30000 AND var50 < 50000) THEN var50 ELSE 0 END) AS AGI_30000to50000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 50000 AND var50 < 100000) THEN var50 ELSE 0 END) AS AGI_50000to100000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 100000 AND var50 < 200000) THEN var50 ELSE 0 END) AS AGI_100000to200000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 200000 AND var50 < 500000) THEN var50 ELSE 0 END) AS AGI_200000to500000 FORMAT COMMA20.,
SUM(CASE WHEN (var50 >= 500000 AND var50 < 1000000) THEN var50 ELSE 0 END) AS AGI_500000to1M FORMAT COMMA20.,
SUM(CASE WHEN var50 >= 1000000 THEN var50 ELSE 0 END) AS AGI_1MorMore FORMAT COMMA20.
FROM WORK.AGI
GROUP BY Tax_Year;
QUIT;

